package com.cicadamob.contact.api.group.dao;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.concurrent.atomic.AtomicInteger;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.cicadamob.contact.api.Utils.ContactConfig;
import com.cicadamob.contact.api.Utils.StringUtil;
import com.cicadamob.contact.api.group.bean.Group;
import com.cicadamob.contact.api.service.AppDbHelper;
import com.cicadamob.contact.api.service.Contact;
import com.cicadamob.contact.api.service.DBManager;
import com.cicadamob.contact.api.service.AppDbHelper.TS_DATA_VERSION;
import com.cicadamob.contact.api.service.AppDbHelper.TS_GROUP_NAME;
import com.cicadamob.contact.api.service.AppDbHelper.TS_GROUP_USER;

/**
 * 群组数据库相关操作
 * 
 * @author Administrator
 * 
 */
public class Group_Db_Dao {
//	private static AppDbHelper dbHelper;
	private Context context;
	private static Group_Db_Dao group_dao = null;
//	private SQLiteDatabase db;
//	private AtomicInteger mOpenCounterRead = new AtomicInteger();
//	private AtomicInteger mOpenCounterWrite = new AtomicInteger();

	private Group_Db_Dao(Context context) {
		this.context = context;
//		db = DBManager.getInstance(context).openDatabase();

	}

	public static synchronized Group_Db_Dao getInstance(Context context) {
		if (group_dao == null) {
			group_dao = new Group_Db_Dao(context);
		}
		return group_dao;

	}

	private synchronized SQLiteDatabase getWritableDatabase() {
//		if (mOpenCounterWrite.incrementAndGet() == 1) {
//			// Opening new database
//			db = DBManager.getInstance(context).openDatabase();
//		}
		return DBManager.getInstance(context).openDatabase();
	}

	private synchronized SQLiteDatabase getReadableDatabase() {
//		if (mOpenCounterRead.incrementAndGet() == 1) {
//			// Opening new database
//			db = DBManager.getInstance(context).openDatabase();
//		}
		return DBManager.getInstance(context).openDatabase();
	}

	private synchronized void closeDB() {
		DBManager.getInstance(context).closeDatabase();
	}

	/**
	 * 根据群组id查询 获取群组成员最新时间戳
	 * 
	 * @param groupid
	 * @return
	 */
	public String getQueryMemberTimeStamp(String groupid) {
		String timeStamp = "";
		SQLiteDatabase db = getReadableDatabase();

		Cursor c = null;
		try {
			c = db.query(AppDbHelper.TABLE_DATA_VERSION,
					new String[] { TS_DATA_VERSION.TIMESTAMP },
					TS_DATA_VERSION.GROUP_ID + "=?", new String[] { groupid },
					null, null, null);
			if (c != null && c.getCount() > 0) {
				if (c.moveToNext()) {
					timeStamp = c.getString(0);
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			closeDB();

		}
		return timeStamp;
	}

	/**
	 * 
	 * 根据群组id查询 获取群组成员最新时间戳
	 * 
	 * 
	 * 
	 * @param groupid
	 * 
	 * @return
	 */

	public String getGroupAndMemberTimeStamp(String groupid) {

		StringBuilder sb = new StringBuilder();

		SQLiteDatabase db = getReadableDatabase();

		Cursor c = null;

		try {

			if (groupid.equals(TS_DATA_VERSION.GROUP_LIST_AND_MEMBER)) {

				c = db.query(AppDbHelper.TABLE_DATA_VERSION,

						new String[] { TS_DATA_VERSION.GROUP_ID,
								TS_DATA_VERSION.TIMESTAMP,
								TS_DATA_VERSION.GROUP_TIME },

						null, null, null, null, null);

				if (c != null && c.getCount() > 0) {

					StringBuilder sb2 = new StringBuilder();

					sb.append("{\"grouplist\":[");

					while (c.moveToNext()) {

						String groupId = c.getString(c
								.getColumnIndex(TS_DATA_VERSION.GROUP_ID));

						String grouptime = c.getString(c
								.getColumnIndex(TS_DATA_VERSION.GROUP_TIME));

						String membertime = c.getString(c
								.getColumnIndex(TS_DATA_VERSION.TIMESTAMP));

						sb2.append("{");

						sb2.append("\"groupid\":").append(groupId).append(",");

						sb2.append("\"grouptime\":").append(grouptime)
								.append(",");

						sb2.append("\"membertime\":").append(membertime);

						sb2.append("}");

						sb2.append(",");

					}

					if (sb2.length() > 0)
						sb2.deleteCharAt(sb2.length() - 1);

					sb.append(sb2);

					sb.append("]}");

				}

			} else {

				c = db.query(AppDbHelper.TABLE_DATA_VERSION,

						new String[] { TS_DATA_VERSION.GROUP_ID,
								TS_DATA_VERSION.TIMESTAMP,
								TS_DATA_VERSION.GROUP_TIME },

						TS_DATA_VERSION.GROUP_ID + "=?",
						new String[] { groupid },

						null, null, null);

				if (c != null && c.getCount() > 0) {

					sb.append("{");

					if (c.moveToNext()) {

						String groupId = c.getString(c
								.getColumnIndex(TS_DATA_VERSION.GROUP_ID));

						String grouptime = c.getString(c
								.getColumnIndex(TS_DATA_VERSION.GROUP_TIME));

						String membertime = c.getString(c
								.getColumnIndex(TS_DATA_VERSION.TIMESTAMP));

						sb.append("\"groupid\":").append(groupId).append(",");

						sb.append("\"grouptime\":").append(grouptime)
								.append(",");

						sb.append("\"membertime\":").append(membertime);

					}

					sb.append("}");

				}

			}

		} catch (Exception e) {

			// TODO Auto-generated catch block

			e.printStackTrace();

		} finally {

			if (c != null) {

				c.close();

			}

			closeDB();

		}

		return sb.toString();

	}

	/**
	 * 查询群组列表的时间戳
	 * 
	 * @return
	 */
	public String getQueryPersonGroupTimeStamp() {
		return getQueryMemberTimeStamp(TS_DATA_VERSION.GROUP_LIST_ID);
	}

	/**
	 * 查询群组列表及成员的时间戳
	 * 
	 * @return
	 */
	public String getQueryGroupAndMemberTimeStamp() {
		return getQueryMemberTimeStamp(TS_DATA_VERSION.GROUP_LIST_AND_MEMBER);
	}

	// 群组相关操作
	/**
	 * 根据群组id 删除群组
	 * 
	 * @param gid
	 * @return
	 */
	public int delGroupById(String gid) {
		int id = 0;
		SQLiteDatabase	db = getWritableDatabase();
		id = db.delete(AppDbHelper.TABLE_GROUP, TS_GROUP_NAME.UUID + "=?",
				new String[] { gid });
		closeDB();
		return id;
	}

	// 群组相关操作
	/**
	 * 根据群组id 删除成员
	 * 
	 * @param gid
	 * @return
	 */
	public int delMemberByGId(String gid) {
		int id = 0;
		SQLiteDatabase db = getWritableDatabase();
		id = db.delete(AppDbHelper.TABLE_GROUP_USER, TS_GROUP_USER.GROUP_ID
				+ "=?", new String[] { gid });
		closeDB();
		return id;
	}

	/**
	 * 删除全部群组
	 */
	public void delAllGroup() {
		SQLiteDatabase	db = getWritableDatabase();
		db.delete(AppDbHelper.TABLE_GROUP, null, null);
		db.delete(AppDbHelper.TABLE_DATA_VERSION, null, null);
		db.delete(AppDbHelper.TABLE_GROUP_USER, null, null);
		closeDB();
	}

	/**
	 * ' 根据群组id更新群组
	 * 
	 * @param values
	 * @param gid
	 */
	public void updateGroupById(ContentValues values, String gid) {
		SQLiteDatabase db = getWritableDatabase();
		db.update(AppDbHelper.TABLE_GROUP, values, TS_GROUP_NAME.UUID + "=?",
				new String[] { gid });
		closeDB();
	}

	/**
	 * 插入群组
	 * 
	 * @param values
	 */
	public void insertGroup(ContentValues values) {
		SQLiteDatabase	db = getWritableDatabase();
		db.isDbLockedByOtherThreads();
		try {
			db.insert(AppDbHelper.TABLE_GROUP, null, values);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeDB();
		}
	}

	/**
	 * 根据群组id 获取群组创建者
	 * 
	 * @param groupid
	 * @return
	 */
	public String selectCreateUserOfGroup(String groupid) {
		String createUser = "";
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = null;

		try {
			c = db.query(AppDbHelper.TABLE_GROUP,
					new String[] { TS_GROUP_NAME.CREATE_USER },
					TS_GROUP_NAME.UUID + "=?", new String[] { groupid }, null,
					null, null);
			if (c != null && c.getCount() > 0) {
				if (c.moveToNext()) {
					createUser = c.getString(0);
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			closeDB();
		}
		return createUser;

	}

	/**
	 * 查询全部群组
	 * 
	 * @return
	 */
	public List<Group> queryAllGroup() {
		List<Group> groups = new ArrayList<Group>();
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = null;
		try {
			c = db.query(AppDbHelper.TABLE_GROUP, null, null, null, null, null,
					TS_GROUP_NAME.STATE + " DESC,"
							+ TS_GROUP_NAME.FNAME_FPY_NUM + " asc");

			HashMap<String, Integer> groupCountMap = queryGroupCount();

			if (c != null && c.getCount() > 0) {
				while (c.moveToNext()) {
					Group group = new Group();
					group.setGroup_id(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.UUID)));
					group.setCreate_user(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.CREATE_USER)));
					group.setFname_fpy_num(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.FNAME_FPY_NUM)));
					group.setGroup_name(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.GROUP_NAME)));
					group.setGroup_remark(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.GROUP_REMARK)));
					group.setGroup_status(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.GROUP_STATUS)));
					group.setLastUpdateTime(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.CREATE_DATE)));
					group.setQpname(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.QPNAME)));
					group.setSpname(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.SPNAME)));
					group.setUsername(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.USERNAME)));
					group.setIsNew(c.getInt(c
							.getColumnIndex(TS_GROUP_NAME.STATE)));
					group.setImagePath(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.IMAGEPATH)));
					group.setMemberCount(groupCountMap.get(group.getGroup_id()));
					group.setAssetId(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.ASSETID)));
					groups.add(group);
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			closeDB();
		}
		return groups;
	}

	/**
	 * 查询全部群组
	 * 
	 * @return
	 */
	public List<Group> queryGroupByName(String keyWord) {
		List<Group> groups = new ArrayList<Group>();
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = null;
		try {
			String keyEncode = "";
			if (!StringUtil.isEmpty(keyWord)) {
				keyEncode = StringUtil.encode(keyWord);
				if (StringUtil.isNum(keyWord)) {
					// // 数字
				} else if (keyEncode.length() == keyWord.length()) {
					// 拼音
					keyWord = keyWord.toLowerCase();// 小写
					// c=db.query(AppDbHelper.TABLE_GROUP,null,
					// TS_GROUP_NAME.SPNAME+"like ? or "+TS_GROUP_NAME.QPNAME+" like ",
					// new String[]{"%"+keyWord+"%","%"+keyWord+"%"}, null,
					// null, TS_GROUP_NAME.UUID+" DESC");
				} else {
					// 中文
				}
			}

			c = db.query(AppDbHelper.TABLE_GROUP, null, TS_GROUP_NAME.SPNAME
					+ " like ? or " + TS_GROUP_NAME.QPNAME + " like ? or "
					+ TS_GROUP_NAME.GROUP_NAME + " like ?", new String[] {
					"%" + keyWord + "%", "%" + keyWord + "%",
					"%" + keyWord + "%" }, null, null, TS_GROUP_NAME.UUID
					+ " DESC");
			if (c != null && c.getCount() > 0) {
				while (c.moveToNext()) {
					Group group = new Group();
					group.setGroup_id(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.UUID)));
					group.setCreate_user(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.CREATE_USER)));
					group.setFname_fpy_num(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.FNAME_FPY_NUM)));
					group.setGroup_name(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.GROUP_NAME)));
					group.setGroup_remark(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.GROUP_REMARK)));
					group.setGroup_status(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.GROUP_STATUS)));
					group.setLastUpdateTime(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.CREATE_DATE)));
					group.setQpname(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.QPNAME)));
					group.setSpname(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.SPNAME)));
					group.setUsername(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.USERNAME)));
					group.setIsNew(c.getInt(c
							.getColumnIndex(TS_GROUP_NAME.STATE)));
					group.setImagePath(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.IMAGEPATH)));

					groups.add(group);
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			closeDB();
		}
		return groups;
	}

	/**
	 * 根据姓名、拼音、电话号码查询成员
	 * 
	 * @return
	 */
	public List<Contact> queryByContact(String str) {
		List<Contact> contacts = new ArrayList<Contact>();
		SQLiteDatabase	db = getReadableDatabase();
		Cursor c = null;
		try {
			// 是否为电话号码
			if (StringUtil.isNumeric(str)) {
				c = db.rawQuery(
						"select * from TS_GROUP_USER where USER_ACCOUNT like '%"
								+ str
								+ "%' group by USER_ACCOUNT order by QPNAME asc",
						null);
			}// 是否为拼音
			else if (StringUtil.isLetter(str)) {
				c = db.rawQuery(
						"select * from TS_GROUP_USER where QPNAME like '%"
								+ str
								+ "%' or SPNAME like '%"
								+ str
								+ "%' group by USER_ACCOUNT order by QPNAME asc",
						null);
			}// 汉字
			else {
				c = db.rawQuery(
						"select * from TS_GROUP_USER where DISPLAYNAME like '%"
								+ str
								+ "%' group by USER_ACCOUNT order by QPNAME asc",
						null);
			}
			if (c != null) {
				while (c.moveToNext()) {
					Contact contact = new Contact();
					contact.setIdInGroup(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GU_UUID)));
					contact.setGroup_id(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GROUP_ID)));
					contact.setUser_type(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USERTYPE)));
					contact.setDisplayName(c.getString(c
							.getColumnIndex(TS_GROUP_USER.DISPLAYNAME)));
					contact.setLinkPhone(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USER_ACCOUNT)));
					contact.setUser_status(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USER_STATUS)));
					contact.setCreate_time(c.getString(c
							.getColumnIndex(TS_GROUP_USER.CREATE_DATE)));
					contact.setCreate_user(c.getString(c
							.getColumnIndex(TS_GROUP_USER.CREATE_USER)));
					contact.setGu_remark(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GU_REMARK)));
					contact.setIsAut(c.getString(c
							.getColumnIndex(TS_GROUP_USER.ISAUT)));
					contact.setFirstPy(c.getString(c
							.getColumnIndex(TS_GROUP_USER.SPNAME)));
					contact.setFnamefpynum(c.getInt(c
							.getColumnIndex(TS_GROUP_USER.FNAME_FPY_NUM)));
					contact.setAllPy(c.getString(c
							.getColumnIndex(TS_GROUP_USER.QPNAME)));
					contact.setCompany(c.getString(c
							.getColumnIndex(TS_GROUP_USER.COMPANY)));
					contact.setDepartment(c.getString(c
							.getColumnIndex(TS_GROUP_USER.DEPARTMENT)));
					contact.setJobprofession(c.getString(c
							.getColumnIndex(TS_GROUP_USER.JOBPROFESSION)));
					contact.setISJTUSER(c.getString(c
							.getColumnIndex(TS_GROUP_USER.ISJTUSER)));
					contact.setInstall(c.getInt(c
							.getColumnIndex(TS_GROUP_USER.INSTALL)));
					contact.setSourceId("4");
					contacts.add(contact);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			closeDB();
		}
		return contacts;
	}

	/**
	 * 根据姓名、拼音、电话号码查询某群组成员
	 * 
	 * @return
	 */
	public List<Contact> queryByKeywordAndGroupId(String groupId, String str) {
		List<Contact> contacts = new ArrayList<Contact>();
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = null;
		try {
			// 是否为电话号码
			if (StringUtil.isNumeric(str)) {
				c = db.rawQuery("select * from TS_GROUP_USER where GROUP_ID="
						+ groupId + " and USER_ACCOUNT like '%" + str
						+ "%' group by USER_ACCOUNT order by QPNAME asc", null);
			}// 是否为拼音
			else if (StringUtil.isLetter(str)) {
				c = db.rawQuery("select * from TS_GROUP_USER where GROUP_ID="
						+ groupId + " and QPNAME like '%" + str
						+ "%' or SPNAME like '%" + str
						+ "%' group by USER_ACCOUNT order by QPNAME asc", null);
			}// 汉字
			else {
				c = db.rawQuery("select * from TS_GROUP_USER where GROUP_ID="
						+ groupId + " and DISPLAYNAME like '%" + str
						+ "%' group by USER_ACCOUNT order by QPNAME asc", null);
			}
			if (c != null) {
				while (c.moveToNext()) {
					Contact contact = new Contact();
					contact.setIdInGroup(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GU_UUID)));
					contact.setGroup_id(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GROUP_ID)));
					contact.setUser_type(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USERTYPE)));
					contact.setDisplayName(c.getString(c
							.getColumnIndex(TS_GROUP_USER.DISPLAYNAME)));
					contact.setLinkPhone(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USER_ACCOUNT)));
					contact.setUser_status(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USER_STATUS)));
					contact.setCreate_time(c.getString(c
							.getColumnIndex(TS_GROUP_USER.CREATE_DATE)));
					contact.setCreate_user(c.getString(c
							.getColumnIndex(TS_GROUP_USER.CREATE_USER)));
					contact.setGu_remark(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GU_REMARK)));
					contact.setIsAut(c.getString(c
							.getColumnIndex(TS_GROUP_USER.ISAUT)));
					contact.setFirstPy(c.getString(c
							.getColumnIndex(TS_GROUP_USER.SPNAME)));
					contact.setFnamefpynum(c.getInt(c
							.getColumnIndex(TS_GROUP_USER.FNAME_FPY_NUM)));
					contact.setAllPy(c.getString(c
							.getColumnIndex(TS_GROUP_USER.QPNAME)));
					contact.setCompany(c.getString(c
							.getColumnIndex(TS_GROUP_USER.COMPANY)));
					contact.setDepartment(c.getString(c
							.getColumnIndex(TS_GROUP_USER.DEPARTMENT)));
					contact.setJobprofession(c.getString(c
							.getColumnIndex(TS_GROUP_USER.JOBPROFESSION)));
					contact.setISJTUSER(c.getString(c
							.getColumnIndex(TS_GROUP_USER.ISJTUSER)));
					contact.setInstall(c.getInt(c
							.getColumnIndex(TS_GROUP_USER.INSTALL)));
					contact.setSourceId("4");
					contacts.add(contact);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			closeDB();
		}
		return contacts;
	}

	/**
	 * 根据群组id和成员的手机号码获取某群组的成员，如果找不到就返回null
	 * 
	 * @param groupId
	 * @param str
	 * @return
	 */
	public Contact getMemberByGroupIdAndTel(String groupId, String str) {
		SQLiteDatabase db = getReadableDatabase();
		Contact contact = null;
		Cursor c = null;
		// c = db.rawQuery("select * from TS_GROUP_USER where GROUP_ID="
		// + groupId + " and USER_ACCOUNT =" + str, null);
		try {
			c = db.query("TS_GROUP_USER", null,
					"GROUP_ID=? and USER_ACCOUNT =?", new String[] { groupId,
							str }, null, null, null);
			if (c.moveToFirst()) {
				contact = new Contact();
				contact.setIdInGroup(c.getString(c
						.getColumnIndex(TS_GROUP_USER.GU_UUID)));
				contact.setGroup_id(c.getString(c
						.getColumnIndex(TS_GROUP_USER.GROUP_ID)));
				contact.setUser_type(c.getString(c
						.getColumnIndex(TS_GROUP_USER.USERTYPE)));
				contact.setDisplayName(c.getString(c
						.getColumnIndex(TS_GROUP_USER.DISPLAYNAME)));
				contact.setLinkPhone(c.getString(c
						.getColumnIndex(TS_GROUP_USER.USER_ACCOUNT)));
				contact.setUser_status(c.getString(c
						.getColumnIndex(TS_GROUP_USER.USER_STATUS)));
				contact.setCreate_time(c.getString(c
						.getColumnIndex(TS_GROUP_USER.CREATE_DATE)));
				contact.setCreate_user(c.getString(c
						.getColumnIndex(TS_GROUP_USER.CREATE_USER)));
				contact.setGu_remark(c.getString(c
						.getColumnIndex(TS_GROUP_USER.GU_REMARK)));
				contact.setIsAut(c.getString(c
						.getColumnIndex(TS_GROUP_USER.ISAUT)));
				contact.setFirstPy(c.getString(c
						.getColumnIndex(TS_GROUP_USER.SPNAME)));
				contact.setFnamefpynum(c.getInt(c
						.getColumnIndex(TS_GROUP_USER.FNAME_FPY_NUM)));
				contact.setAllPy(c.getString(c
						.getColumnIndex(TS_GROUP_USER.QPNAME)));
				contact.setCompany(c.getString(c
						.getColumnIndex(TS_GROUP_USER.COMPANY)));
				contact.setDepartment(c.getString(c
						.getColumnIndex(TS_GROUP_USER.DEPARTMENT)));
				contact.setJobprofession(c.getString(c
						.getColumnIndex(TS_GROUP_USER.JOBPROFESSION)));
				contact.setISJTUSER(c.getString(c
						.getColumnIndex(TS_GROUP_USER.ISJTUSER)));
				contact.setInstall(c.getInt(c
						.getColumnIndex(TS_GROUP_USER.INSTALL)));
				contact.setSourceId("4");
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			closeDB();
		}
		return contact;
	}

	/**
	 * 根据姓名、拼音、电话号码查询所有群组成员
	 * 
	 * @return
	 */
	public List<Contact> queryByKeywordAllGroupUser(String groupId, String str) {
		List<Contact> contacts = new ArrayList<Contact>();
		SQLiteDatabase	db = getReadableDatabase();
		Cursor c = null;
		try {
			// 是否为电话号码
			if (StringUtil.isNumeric(str)) {
				c = db.rawQuery("select * from TS_GROUP_USER where GROUP_ID="
						+ groupId + " and USER_ACCOUNT like '%" + str
						+ "%' group by USER_ACCOUNT order by QPNAME asc", null);
			}// 是否为拼音
			else if (StringUtil.isLetter(str)) {
				c = db.rawQuery("select * from TS_GROUP_USER where GROUP_ID="
						+ groupId + " and QPNAME like '%" + str
						+ "%' or SPNAME like '%" + str
						+ "%' group by USER_ACCOUNT order by QPNAME asc", null);
			}// 汉字
			else {
				c = db.rawQuery("select * from TS_GROUP_USER where GROUP_ID="
						+ groupId + " and DISPLAYNAME like '%" + str
						+ "%' group by USER_ACCOUNT order by QPNAME asc", null);
			}
			if (c != null) {
				while (c.moveToNext()) {
					Contact contact = new Contact();
					contact.setIdInGroup(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GU_UUID)));
					contact.setGroup_id(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GROUP_ID)));
					contact.setUser_type(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USERTYPE)));
					contact.setDisplayName(c.getString(c
							.getColumnIndex(TS_GROUP_USER.DISPLAYNAME)));
					contact.setLinkPhone(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USER_ACCOUNT)));
					contact.setUser_status(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USER_STATUS)));
					contact.setCreate_time(c.getString(c
							.getColumnIndex(TS_GROUP_USER.CREATE_DATE)));
					contact.setCreate_user(c.getString(c
							.getColumnIndex(TS_GROUP_USER.CREATE_USER)));
					contact.setGu_remark(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GU_REMARK)));
					contact.setIsAut(c.getString(c
							.getColumnIndex(TS_GROUP_USER.ISAUT)));
					contact.setFirstPy(c.getString(c
							.getColumnIndex(TS_GROUP_USER.SPNAME)));
					contact.setFnamefpynum(c.getInt(c
							.getColumnIndex(TS_GROUP_USER.FNAME_FPY_NUM)));
					contact.setAllPy(c.getString(c
							.getColumnIndex(TS_GROUP_USER.QPNAME)));
					contact.setCompany(c.getString(c
							.getColumnIndex(TS_GROUP_USER.COMPANY)));
					contact.setDepartment(c.getString(c
							.getColumnIndex(TS_GROUP_USER.DEPARTMENT)));
					contact.setJobprofession(c.getString(c
							.getColumnIndex(TS_GROUP_USER.JOBPROFESSION)));
					contact.setISJTUSER(c.getString(c
							.getColumnIndex(TS_GROUP_USER.ISJTUSER)));
					contact.setInstall(c.getInt(c
							.getColumnIndex(TS_GROUP_USER.INSTALL)));
					contact.setSourceId("4");
					contacts.add(contact);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			closeDB();
		}
		return contacts;
	}

	/**
	 * 根据电话查询出人的名字
	 * 
	 * @param tel
	 * @return
	 */
	public Contact queryMemberByTel(String tel) {
		SQLiteDatabase db = getReadableDatabase();
		String name = null;
		Cursor c = null;
		Contact contact = null;
		try {
			c = db.query(AppDbHelper.TABLE_GROUP_USER, null,
					TS_GROUP_USER.USER_ACCOUNT + "=?", new String[] { tel },
					null, null, null);
			contact = new Contact();
			while (c.moveToNext()) {
				name = c.getString(c.getColumnIndex(TS_GROUP_USER.DISPLAYNAME));
				contact.setSourceId("4");
				contact.setDisplayName(name);
				return contact;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			closeDB();
		}
		return null;
	}

	/**
	 * 根据分组查询全部联系人
	 * 
	 * @param groupId
	 * @return
	 */
	public List<Contact> queryAllMemberByGroupId(String groupId) {
		List<Contact> contacts = new ArrayList<Contact>();
		SQLiteDatabase db = getReadableDatabase();

		Cursor c = null;
		try {
			c = db.query(AppDbHelper.TABLE_GROUP_USER, null,
					TS_GROUP_USER.GROUP_ID + "=?", new String[] { groupId },
					null, null, TS_GROUP_USER.FNAME_FPY_NUM + " ASC ");
			if (c != null && c.getCount() > 0) {
				while (c.moveToNext()) {
					Contact contact = new Contact();
					contact.setIdInGroup(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GU_UUID)));
					contact.setGroup_id(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GROUP_ID)));
					contact.setUser_type(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USERTYPE)));
					contact.setDisplayName(c.getString(c
							.getColumnIndex(TS_GROUP_USER.DISPLAYNAME)));
					contact.setLinkPhone(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USER_ACCOUNT)));
					contact.setUser_status(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USER_STATUS)));
					contact.setCreate_time(c.getString(c
							.getColumnIndex(TS_GROUP_USER.CREATE_DATE)));
					contact.setCreate_user(c.getString(c
							.getColumnIndex(TS_GROUP_USER.CREATE_USER)));
					contact.setGu_remark(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GU_REMARK)));
					contact.setIsAut(c.getString(c
							.getColumnIndex(TS_GROUP_USER.ISAUT)));
					contact.setInstall(c.getInt(c
							.getColumnIndex(TS_GROUP_USER.INSTALL)));
					String firstPy = c.getString(c
							.getColumnIndex(TS_GROUP_USER.SPNAME));
					contact.setFirstPy(firstPy);
					// if(StringUtil.isNum(firstPy.substring(0, 1)))
					// contact.setFnamefpy("#");
					// else
					// contact.setFnamefpy(firstPy.substring(0,
					// 1).toUpperCase());

					char[] firstChar = firstPy.toCharArray();
					if (firstChar != null && firstChar.length > 0) {
						int tenChar = firstChar[0];
						if (tenChar > 64 && tenChar < 91) {
							contact.setLetter(String.valueOf(firstChar[0]));
							contact.setFnamefpy(String.valueOf(firstChar[0]));
						} else if (tenChar > 96 && tenChar < 123) {
							contact.setLetter(String.valueOf(firstChar[0])
									.toUpperCase());
							contact.setFnamefpy(String.valueOf(firstChar[0])
									.toUpperCase());
						} else {
							contact.setLetter("#");
							contact.setFnamefpy("#");
						}
					}
					contact.setFirstPy(firstPy);
					contact.setFnamefpynum(c.getInt(c
							.getColumnIndex(TS_GROUP_USER.FNAME_FPY_NUM)));
					contact.setAllPy(c.getString(c
							.getColumnIndex(TS_GROUP_USER.QPNAME)));
					contact.setCompany(c.getString(c
							.getColumnIndex(TS_GROUP_USER.COMPANY)));
					contact.setCompanyPath(c.getString(c
							.getColumnIndex(TS_GROUP_USER.COMPANY)));
					contact.setDepartment(c.getString(c
							.getColumnIndex(TS_GROUP_USER.DEPARTMENT)));
					contact.setJobprofession(c.getString(c
							.getColumnIndex(TS_GROUP_USER.JOBPROFESSION)));
					contact.setISJTUSER(c.getString(c
							.getColumnIndex(TS_GROUP_USER.ISJTUSER)));
					contact.setSourceId("4");
					// 如果成员是自己，则修改信息
					if (contact.getLinkPhone()
							.equals(ContactConfig.getMobile(ContactConfig
									.getContext()))) {
						contact.setFnamefpy("我");
						contact.setLetter("我");
						// contact.setFirstPy(firstPy);
						contacts.add(0, contact);
					} else {
						contacts.add(contact);
					}

				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			closeDB();
		}
		return contacts;
	}

	/**
	 * 根据成员id删除成员
	 * 
	 * @param mid
	 * @return
	 */
	public int delMemberByMid(String mid) {
		int id = 0;
		try {
			SQLiteDatabase	db = getWritableDatabase();
			id = db.delete(AppDbHelper.TABLE_GROUP_USER, TS_GROUP_USER.GU_UUID
					+ "=?", new String[] { mid });
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeDB();
		}

		return id;

	}

	/**
	 * 插入成员
	 * 
	 * @param contact
	 * @param isTemp
	 *            是否是临时 在从网上获取成员时 要删除临时成员
	 * @return
	 */
	public long insertMember(Contact contact, boolean isTemp) {

		long id = 0;
		try {
			SQLiteDatabase db = getWritableDatabase();
			ContentValues values = new ContentValues();
			values.put(TS_GROUP_USER.GU_UUID, contact.getIdInGroup());
			values.put(TS_GROUP_USER.GROUP_ID, contact.getGroup_id());
			values.put(TS_GROUP_USER.USERTYPE, contact.getUser_type());
			values.put(TS_GROUP_USER.DISPLAYNAME, contact.getDisplayName());
			values.put(TS_GROUP_USER.USER_ACCOUNT, contact.getLinkPhone());
			values.put(TS_GROUP_USER.USER_STATUS, contact.getUser_status());
			values.put(TS_GROUP_USER.CREATE_DATE, contact.getCreate_time());
			values.put(TS_GROUP_USER.CREATE_USER, contact.getCreate_user());
			values.put(TS_GROUP_USER.GU_REMARK, contact.getGu_remark());
			values.put(TS_GROUP_USER.ISAUT, contact.getIsAut());
			values.put(TS_GROUP_USER.SPNAME, contact.getFirstPy());
			values.put(TS_GROUP_USER.FNAME_FPY_NUM, contact.getFnamefpynum());
			values.put(TS_GROUP_USER.QPNAME, contact.getAllPy());
			values.put(TS_GROUP_USER.COMPANY, contact.getCompany());
			values.put(TS_GROUP_USER.DEPARTMENT, contact.getDepartment());
			values.put(TS_GROUP_USER.JOBPROFESSION, contact.getJobprofession());
			values.put(TS_GROUP_USER.ISJTUSER, contact.getISJTUSER());
			values.put(TS_GROUP_USER.INSTALL, contact.getInstall());
			if (isTemp) {
				values.put(TS_GROUP_USER.ISTEMP, "1");
			} else {
				values.put(TS_GROUP_USER.ISTEMP, "0");
			}
			id = db.insert(AppDbHelper.TABLE_GROUP_USER, null, values);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeDB();
		}
		return id;
	}

	/**
	 * 批量插入数据库
	 * 
	 * @param contacts
	 */
	public void insertMembers(List<Contact> contacts) {
		SQLiteDatabase db = getWritableDatabase();
		for (Contact contact : contacts) {
			try {
				ContentValues values = new ContentValues();
				values.put(TS_GROUP_USER.GU_UUID, contact.getIdInGroup());
				values.put(TS_GROUP_USER.GROUP_ID, contact.getGroup_id());
				values.put(TS_GROUP_USER.USERTYPE, contact.getUser_type());
				values.put(TS_GROUP_USER.DISPLAYNAME, contact.getDisplayName());
				values.put(TS_GROUP_USER.USER_ACCOUNT, contact.getLinkPhone());
				values.put(TS_GROUP_USER.USER_STATUS, contact.getUser_status());
				values.put(TS_GROUP_USER.CREATE_DATE, contact.getCreate_time());
				values.put(TS_GROUP_USER.CREATE_USER, contact.getCreate_user());
				values.put(TS_GROUP_USER.GU_REMARK, contact.getGu_remark());
				values.put(TS_GROUP_USER.ISAUT, contact.getIsAut());
				values.put(TS_GROUP_USER.SPNAME, contact.getFirstPy());
				values.put(TS_GROUP_USER.FNAME_FPY_NUM,
						contact.getFnamefpynum());
				values.put(TS_GROUP_USER.QPNAME, contact.getAllPy());
				values.put(TS_GROUP_USER.COMPANY, contact.getCompanyPath());
				values.put(TS_GROUP_USER.DEPARTMENT, contact.getDepartment());
				values.put(TS_GROUP_USER.JOBPROFESSION,
						contact.getJobprofession());
				values.put(TS_GROUP_USER.ISJTUSER, contact.getISJTUSER());
				values.put(TS_GROUP_USER.INSTALL, contact.getInstall());
				db.insert(AppDbHelper.TABLE_GROUP_USER, null, values);
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
				continue;
			} finally {
				closeDB();
			}
		}

		/*
		 * if(db!=null){ db.close(); }
		 */
	}

	/**
	 * 修改成员信息
	 * 
	 * @param values
	 * @param id
	 * @return
	 */
	public long updateMemberById(ContentValues values, String id) {
		try {
			SQLiteDatabase	db = getWritableDatabase();
			long count = db.update(AppDbHelper.TABLE_GROUP_USER, values,
					TS_GROUP_USER.GU_UUID + "=?", new String[] { id });
			return count;
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeDB();
		}
		return 0;
	}

	/**
	 * 执行sql语句
	 * 
	 * @param sql
	 */
	public void executesql(String sql) {
		try {
			SQLiteDatabase	db = getWritableDatabase();
			db.execSQL(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeDB();
		}
	}

	/**
	 * 批量执行sql语句
	 * 
	 * @param sql
	 */
	public void executesql(List<String> sqlList) {
		/*
		 * db=getWritableDatabase(); if(sqlList==null){ return; } for(String sql
		 * :sqlList){ try { db.execSQL(sql); } catch (SQLException e) { // TODO
		 * Auto-generated catch block e.printStackTrace(); continue; } }
		 */
		if (sqlList == null || sqlList.size() == 0) {
			return;
		}

		SQLiteDatabase	db = getWritableDatabase();

		db.beginTransaction(); // 手动设置开始事务
		try {
			// 批量处理操作
			for (String sql : sqlList) {
				try {
					db.execSQL(sql);
				} catch (Exception e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
					continue;
				}
			}
			db.setTransactionSuccessful(); // 设置事务处理成功，不设置会自动回滚不提交
		} catch (Exception e) {

		} finally {
			db.endTransaction(); // 处理完成
			closeDB();
		}
	}

	/**
	 * 更新分组的时间戳
	 * 
	 * @param groupId
	 * @param timestmp
	 */
	public void updateVersion(String groupId, String timestmp) {
		try {
			SQLiteDatabase db = getWritableDatabase();

			ContentValues values = new ContentValues();
			values.put(TS_DATA_VERSION.GROUP_ID, groupId);
			values.put(TS_DATA_VERSION.TIMESTAMP, timestmp);
			values.put(TS_DATA_VERSION.GROUP_TIME, timestmp);
			if (groupVersionExist(groupId)) {
				db.update(AppDbHelper.TABLE_DATA_VERSION, values,
						TS_DATA_VERSION.GROUP_ID + "=?",
						new String[] { groupId });
			} else {
				db.insert(AppDbHelper.TABLE_DATA_VERSION, null, values);
				/*
				 * db.update(AppDbHelper.TABLE_DATA_VERSION, values,
				 * TS_DATA_VERSION.GROUP_ID + "=?", new String[] { groupId });
				 */
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeDB();
		}

	}

	public boolean groupVersionExist(String groupId) {
		boolean exist = false;
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = null;
		try {
			c = db.query(AppDbHelper.TABLE_DATA_VERSION, null,
					TS_DATA_VERSION.GROUP_ID + "=?", new String[] { groupId },
					null, null, null);
			if (c != null && c.getCount() > 0) {
				exist = true;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			closeDB();
		}
		return exist;
	}

	/**
	 * 根据分组id 电话号码判断是不是创建者
	 * 
	 * @param phone
	 * @param groupId
	 * @return
	 */
	public boolean isGroupCreater(String phone, String groupId) {

		boolean isCreater = false;
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = null;
		try {
			// c=db.query(AppDbHelper.TABLE_GROUP_USER,null,
			// TS_GROUP_USER.GROUP_ID+"=? and "+TS_GROUP_USER.USER_ACCOUNT="",
			// new String[]{groupId}, null, null, null);
			c = db.query(AppDbHelper.TABLE_GROUP, null,
					TS_GROUP_NAME.CREATE_USER + "=? and " + TS_GROUP_NAME.UUID
							+ "=?", new String[] { phone, groupId }, null,
					null, null);
			if (c != null && c.getCount() > 0) {
				isCreater = true;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			closeDB();
		}
		return isCreater;
	}

	/**
	 * 根据群组id 删除相关version
	 * 
	 * @param gid
	 * @return
	 */
	public int delGroupVersionByGId(String gid) {
		int id = 0;
		try {
			SQLiteDatabase	db = getWritableDatabase();
			id = db.delete(AppDbHelper.TABLE_DATA_VERSION,
					TS_DATA_VERSION.GROUP_ID + "=?", new String[] { gid });
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeDB();
		}

		return id;
	}

	/**
	 * 删除本地添加的临时成员
	 * 
	 * @param gid
	 * @return
	 */
	public int delTempMember(String gid) {
		int id = 0;
		try {
			SQLiteDatabase db = getWritableDatabase();
			id = db.delete(AppDbHelper.TABLE_GROUP_USER, TS_GROUP_USER.ISTEMP
					+ "=? and " + TS_GROUP_USER.GROUP_ID + "=?", new String[] {
					"1", gid });
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeDB();
		}

		return id;
	}

	/**
	 * 根据分组查询全部联系人
	 * 
	 * @param groupId
	 * @return
	 */
	public List<Contact> queryAllMemberByGroupId(String groupId, String order) {
		List<Contact> contacts = new ArrayList<Contact>();
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = null;
		try {
			c = db.query(AppDbHelper.TABLE_GROUP_USER, null,
					TS_GROUP_USER.GROUP_ID + "=?", new String[] { groupId },
					null, null, order + " asc " + ","
							+ TS_GROUP_USER.FNAME_FPY_NUM + " asc");
			if (c != null && c.getCount() > 0) {
				while (c.moveToNext()) {
					Contact contact = new Contact();
					contact.setIdInGroup(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GU_UUID)));
					contact.setGroup_id(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GROUP_ID)));
					contact.setUser_type(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USERTYPE)));
					contact.setDisplayName(c.getString(c
							.getColumnIndex(TS_GROUP_USER.DISPLAYNAME)));
					contact.setLinkPhone(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USER_ACCOUNT)));
					contact.setUser_status(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USER_STATUS)));
					contact.setCreate_time(c.getString(c
							.getColumnIndex(TS_GROUP_USER.CREATE_DATE)));
					contact.setCreate_user(c.getString(c
							.getColumnIndex(TS_GROUP_USER.CREATE_USER)));
					contact.setGu_remark(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GU_REMARK)));
					contact.setIsAut(c.getString(c
							.getColumnIndex(TS_GROUP_USER.ISAUT)));
					contact.setFirstPy(c.getString(c
							.getColumnIndex(TS_GROUP_USER.SPNAME)));
					contact.setFnamefpynum(c.getInt(c
							.getColumnIndex(TS_GROUP_USER.FNAME_FPY_NUM)));
					contact.setAllPy(c.getString(c
							.getColumnIndex(TS_GROUP_USER.QPNAME)));
					contact.setCompany(c.getString(c
							.getColumnIndex(TS_GROUP_USER.COMPANY)));
					contact.setCompanyPath(c.getString(c
							.getColumnIndex(TS_GROUP_USER.COMPANY)));

					contact.setDepartment(c.getString(c
							.getColumnIndex(TS_GROUP_USER.DEPARTMENT)));
					contact.setJobprofession(c.getString(c
							.getColumnIndex(TS_GROUP_USER.JOBPROFESSION)));
					contact.setISJTUSER(c.getString(c
							.getColumnIndex(TS_GROUP_USER.ISJTUSER)));
					contact.setInstall(c.getInt(c
							.getColumnIndex(TS_GROUP_USER.INSTALL)));

					contact.setSourceId("4");
					contacts.add(contact);
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			closeDB();
		}
		return contacts;
	}

	/**
	 * 删除群组相关信息
	 */
	public void clear_group_db() {
		try {
			SQLiteDatabase	db = getWritableDatabase();
			db.delete(AppDbHelper.TABLE_GROUP, null, null);
			db.delete(AppDbHelper.TABLE_GROUP_USER, null, null);
			db.delete(AppDbHelper.TABLE_DATA_VERSION, null, null);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeDB();
		}

	}

	public String getMemberIdInGroup(String phone, String groupid) {
		String id = "";
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = null;
		try {
			c = db.query(AppDbHelper.TABLE_GROUP_USER,
					new String[] { TS_GROUP_USER.GU_UUID },
					TS_GROUP_USER.GROUP_ID + "=? and "
							+ TS_GROUP_USER.USER_ACCOUNT + " =?", new String[] {
							groupid, phone }, null, null, null);
			if (c != null && c.getCount() > 0) {
				if (c.moveToNext()) {
					id = c.getString(0);
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			closeDB();
		}
		return id;

	}

	/**
	 * 根据群组id查询群组名称及头像路径
	 * 
	 * @param groupid
	 * @return
	 */
	public Group queryGroupNameAndImage(String groupid) {
		Group group = null;
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = null;
		try {
			c = db.query(AppDbHelper.TABLE_GROUP, new String[] {
					TS_GROUP_NAME.GROUP_NAME, TS_GROUP_NAME.IMAGEPATH },
					TS_GROUP_NAME.UUID + "=?", new String[] { groupid }, null,
					null, null);
			if (c != null && c.getCount() > 0) {
				if (c.moveToNext()) {
					group = new Group();
					group.setGroup_name(c.getString(0));
					group.setImagePath(c.getString(1));
					group.setGroup_id(groupid);
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			closeDB();
		}
		return group;
	}

	/**
	 * 根据群组id修改群组状态
	 * 
	 * @param groupid
	 * @return
	 */
	public String updateGroupStateById(String groupid) {
		String groupName = "";
		try {
			SQLiteDatabase	db = getWritableDatabase();
			ContentValues values = new ContentValues();
			values.put(TS_GROUP_NAME.STATE, false);
			db.update(AppDbHelper.TABLE_GROUP, values, TS_GROUP_NAME.UUID
					+ "=?", new String[] { groupid });
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeDB();
		}

		return groupName;
	}

	/**
	 * 
	 * @param contact
	 * @param id
	 * @return
	 */
	public long updateMemberById(Contact contact, String id) {

		SQLiteDatabase db = getWritableDatabase();
		long count = 0;
		try {
			ContentValues values = new ContentValues();
			// values.put(TS_GROUP_USER.GU_UUID, contact.getIdInGroup());
			values.put(TS_GROUP_USER.GROUP_ID, contact.getGroup_id());
			values.put(TS_GROUP_USER.USERTYPE, contact.getUser_type());
			values.put(TS_GROUP_USER.DISPLAYNAME, contact.getDisplayName());
			values.put(TS_GROUP_USER.USER_ACCOUNT, contact.getLinkPhone());
			values.put(TS_GROUP_USER.USER_STATUS, contact.getUser_status());
			values.put(TS_GROUP_USER.CREATE_DATE, contact.getCreate_time());
			values.put(TS_GROUP_USER.CREATE_USER, contact.getCreate_user());
			values.put(TS_GROUP_USER.GU_REMARK, contact.getGu_remark());
			values.put(TS_GROUP_USER.ISAUT, contact.getIsAut());
			values.put(TS_GROUP_USER.SPNAME, contact.getFirstPy());
			values.put(TS_GROUP_USER.FNAME_FPY_NUM, contact.getFnamefpynum());
			values.put(TS_GROUP_USER.QPNAME, contact.getAllPy());
			values.put(TS_GROUP_USER.COMPANY, contact.getCompanyPath());
			values.put(TS_GROUP_USER.DEPARTMENT, contact.getDepartment());
			values.put(TS_GROUP_USER.JOBPROFESSION, contact.getJobprofession());
			values.put(TS_GROUP_USER.ISJTUSER, contact.getISJTUSER());
			values.put(TS_GROUP_USER.INSTALL, contact.getInstall());
			count = db.update(AppDbHelper.TABLE_GROUP_USER, values,
					TS_GROUP_USER.GU_UUID + "=?", new String[] { id });
			return count;
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			closeDB();
		}
		return 0;
	}

	/**
	 * 根据分组查询全部联系人
	 * 
	 * @param groupId
	 * @param OnlyNeedInstalled
	 *            仅仅需要安装了的
	 * @return
	 */
	public List<Contact> queryAllMemberByGroupIdForSelect(String groupId,
			boolean OnlyNeedInstalled) {
		List<Contact> contacts = new ArrayList<Contact>();
		SQLiteDatabase db = getReadableDatabase();
		String expandWhere = "";
		if (OnlyNeedInstalled) {
			expandWhere = " and TS_GROUP_USER.INSTALL=1 ";
		}
		Cursor c = null;
		try {
			c = db.query(AppDbHelper.TABLE_GROUP_USER, null,
					TS_GROUP_USER.GROUP_ID + "=?" + expandWhere,
					new String[] { groupId }, null, null,
					TS_GROUP_USER.FNAME_FPY_NUM + " ASC ");
			if (c != null && c.getCount() > 0) {
				while (c.moveToNext()) {
					Contact contact = new Contact();
					contact.setIdInGroup(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GU_UUID)));
					contact.setGroup_id(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GROUP_ID)));
					contact.setUser_type("2");
					contact.setDisplayName(c.getString(c
							.getColumnIndex(TS_GROUP_USER.DISPLAYNAME)));
					contact.setLinkPhone(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USER_ACCOUNT)));
					contact.setUser_status(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USER_STATUS)));
					contact.setCreate_time(c.getString(c
							.getColumnIndex(TS_GROUP_USER.CREATE_DATE)));
					contact.setCreate_user(c.getString(c
							.getColumnIndex(TS_GROUP_USER.CREATE_USER)));
					contact.setGu_remark(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GU_REMARK)));
					contact.setIsAut(c.getString(c
							.getColumnIndex(TS_GROUP_USER.ISAUT)));
					contact.setInstall(c.getInt(c
							.getColumnIndex(TS_GROUP_USER.INSTALL)));
					String firstPy = c.getString(c
							.getColumnIndex(TS_GROUP_USER.SPNAME));
					contact.setFirstPy(firstPy);

					// if(StringUtil.isNum(firstPy.substring(0, 1)))
					// contact.setFnamefpy("#");
					// else
					// contact.setFnamefpy(firstPy.substring(0,
					// 1).toUpperCase());

					char[] firstChar = firstPy.toCharArray();
					if (firstChar != null && firstChar.length > 0) {
						int tenChar = firstChar[0];
						if (tenChar > 64 && tenChar < 91) {
							contact.setLetter(String.valueOf(firstChar[0]));
							contact.setFnamefpy(String.valueOf(firstChar[0]));
						} else if (tenChar > 96 && tenChar < 123) {
							contact.setLetter(String.valueOf(firstChar[0])
									.toUpperCase());
							contact.setFnamefpy(String.valueOf(firstChar[0])
									.toUpperCase());
						} else {
							contact.setLetter("#");
							contact.setFnamefpy("#");
						}
					}
					contact.setFirstPy(firstPy);

					contact.setFnamefpynum(c.getInt(c
							.getColumnIndex(TS_GROUP_USER.FNAME_FPY_NUM)));
					contact.setAllPy(c.getString(c
							.getColumnIndex(TS_GROUP_USER.QPNAME)));
					contact.setCompany(c.getString(c
							.getColumnIndex(TS_GROUP_USER.COMPANY)));
					contact.setCompanyPath(c.getString(c
							.getColumnIndex(TS_GROUP_USER.COMPANY)));
					contact.setDepartment(c.getString(c
							.getColumnIndex(TS_GROUP_USER.DEPARTMENT)));
					contact.setJobprofession(c.getString(c
							.getColumnIndex(TS_GROUP_USER.JOBPROFESSION)));
					contact.setISJTUSER(c.getString(c
							.getColumnIndex(TS_GROUP_USER.ISJTUSER)));
					contact.setSourceId("4");
					contacts.add(contact);
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			closeDB();
		}
		return contacts;
	}

	/**
	 * 根据姓名、拼音、电话号码查询成员
	 * 
	 * @return
	 */
	public List<Contact> queryByContactForSelect(String str) {
		List<Contact> contacts = new ArrayList<Contact>();
		SQLiteDatabase	db = getReadableDatabase();
		Cursor c = null;
		try {
			// 是否为电话号码
			if (StringUtil.isNumeric(str)) {
				c = db.rawQuery(
						"select * from TS_GROUP_USER where USER_ACCOUNT like '%"
								+ str
								+ "%' group by USER_ACCOUNT order by QPNAME asc",
						null);
			}// 是否为拼音
			else if (StringUtil.isLetter(str)) {
				c = db.rawQuery(
						"select * from TS_GROUP_USER where QPNAME like '%"
								+ str
								+ "%' or SPNAME like '%"
								+ str
								+ "%' group by USER_ACCOUNT order by QPNAME asc",
						null);
			}// 汉字
			else {
				c = db.rawQuery(
						"select * from TS_GROUP_USER where DISPLAYNAME like '%"
								+ str
								+ "%' group by USER_ACCOUNT order by QPNAME asc",
						null);
			}
			if (c != null) {
				while (c.moveToNext()) {
					Contact contact = new Contact();
					contact.setIdInGroup(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GU_UUID)));
					contact.setGroup_id(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GROUP_ID)));
					contact.setUser_type("2");
					contact.setDisplayName(c.getString(c
							.getColumnIndex(TS_GROUP_USER.DISPLAYNAME)));
					contact.setLinkPhone(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USER_ACCOUNT)));
					contact.setUser_status(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USER_STATUS)));
					contact.setCreate_time(c.getString(c
							.getColumnIndex(TS_GROUP_USER.CREATE_DATE)));
					contact.setCreate_user(c.getString(c
							.getColumnIndex(TS_GROUP_USER.CREATE_USER)));
					contact.setGu_remark(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GU_REMARK)));
					contact.setIsAut(c.getString(c
							.getColumnIndex(TS_GROUP_USER.ISAUT)));
					contact.setFirstPy(c.getString(c
							.getColumnIndex(TS_GROUP_USER.SPNAME)));
					contact.setFnamefpynum(c.getInt(c
							.getColumnIndex(TS_GROUP_USER.FNAME_FPY_NUM)));
					contact.setAllPy(c.getString(c
							.getColumnIndex(TS_GROUP_USER.QPNAME)));
					contact.setCompany(c.getString(c
							.getColumnIndex(TS_GROUP_USER.COMPANY)));
					contact.setDepartment(c.getString(c
							.getColumnIndex(TS_GROUP_USER.DEPARTMENT)));
					contact.setJobprofession(c.getString(c
							.getColumnIndex(TS_GROUP_USER.JOBPROFESSION)));
					contact.setISJTUSER(c.getString(c
							.getColumnIndex(TS_GROUP_USER.ISJTUSER)));
					contact.setInstall(c.getInt(c
							.getColumnIndex(TS_GROUP_USER.INSTALL)));
					contact.setSourceId("4");
					contacts.add(contact);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			closeDB();
		}
		return contacts;
	}

	public void delAllMember() {
		try {
			SQLiteDatabase db = getWritableDatabase();
			db.delete(AppDbHelper.TABLE_GROUP_USER, null, null);
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			closeDB();
		}

	}

	public void delMemberByGroupID(String groupId) {
		try {
			SQLiteDatabase	db = getWritableDatabase();
			db.delete(AppDbHelper.TABLE_GROUP_USER, TS_GROUP_USER.GROUP_ID
					+ "=?", new String[] { groupId });
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeDB();
		}

	}

	/**
	 * 根据群组id 删除相关version
	 * 
	 * @param gid
	 * @return
	 */
	public void delGroupVersion() {
		try {
			SQLiteDatabase db = getWritableDatabase();
			db.delete(AppDbHelper.TABLE_DATA_VERSION, null, null);
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			closeDB();
		}

	}

	public void setBeatGroupVersion(String lastTime) {

	}

	/**
	 * 根据群组id修改群组状态
	 * 
	 * @param groupid
	 * @return
	 */
	public String updateGroupStateById(String groupid, int isNew) {
		String groupName = "";
		try {
			SQLiteDatabase db = getWritableDatabase();
			ContentValues values = new ContentValues();
			values.put(TS_GROUP_NAME.STATE, isNew);
			db.update(AppDbHelper.TABLE_GROUP, values, TS_GROUP_NAME.UUID
					+ "=?", new String[] { groupid });
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			closeDB();
		}
		return groupName;
	}

	/**
	 * 查询旧的分组id
	 * 
	 * @return
	 */
	public List<String> queryOlderGroupIds() {
		List<String> groupIds = new ArrayList<String>();
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = null;
		try {
			c = db.query(AppDbHelper.TABLE_GROUP, null, null, null, null, null,
					TS_GROUP_NAME.UUID + " DESC");
			if (c != null && c.getCount() > 0) {
				while (c.moveToNext()) {
					groupIds.add(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.UUID)));
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			closeDB();
		}
		return groupIds;
	}

	/**
	 * 将群组状态全部改成已查看
	 */
	public void updateAllGroupState() {
		try {
			SQLiteDatabase db = getReadableDatabase();
			ContentValues values = new ContentValues();
			values.put(TS_GROUP_NAME.STATE, false);
			db.update(AppDbHelper.TABLE_GROUP, values, null, null);
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			closeDB();
		}

	}

	/**
	 * 更新分组的时间戳
	 * 
	 * @param groupId
	 * @param timestmp
	 */
	public void updateVersionNoCreate(String groupId, String timestmp) {
		try {
			SQLiteDatabase 	db = getReadableDatabase();
			ContentValues values = new ContentValues();
			values.put(TS_DATA_VERSION.GROUP_ID, groupId);
			values.put(TS_DATA_VERSION.TIMESTAMP, timestmp);
			if (groupVersionExist(groupId)) {
				db.update(AppDbHelper.TABLE_DATA_VERSION, values,
						TS_DATA_VERSION.GROUP_ID + "=?",
						new String[] { groupId });
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			closeDB();
		}

	}

	/**
	 * 根据姓名、拼音、电话号码查询成员
	 * 
	 * @return
	 */
	public List<Contact> queryGroupMemberForSelect(String str, String groupid) {
		List<Contact> contacts = new ArrayList<Contact>();
		SQLiteDatabase db = getReadableDatabase();
		Cursor c = null;
		try {
			// 是否为电话号码
			if (StringUtil.isNumeric(str)) {
				c = db.rawQuery(
						"select * from TS_GROUP_USER where USER_ACCOUNT like '%"
								+ str + "%' and GROUP_ID=" + groupid
								+ " group by USER_ACCOUNT order by QPNAME asc",
						null);
			}// 是否为拼音
			else if (StringUtil.isLetter(str)) {
				c = db.rawQuery(
						"select * from TS_GROUP_USER where QPNAME like '%"
								+ str + "%' or SPNAME like '%" + str
								+ "%' and GROUP_ID=" + groupid
								+ " group by USER_ACCOUNT order by QPNAME asc",
						null);
			}// 汉字
			else {
				c = db.rawQuery(
						"select * from TS_GROUP_USER where DISPLAYNAME like '%"
								+ str + "%' and GROUP_ID=" + groupid
								+ " group by USER_ACCOUNT order by QPNAME asc",
						null);
			}
			if (c != null) {
				while (c.moveToNext()) {
					Contact contact = new Contact();
					contact.setIdInGroup(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GU_UUID)));
					contact.setGroup_id(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GROUP_ID)));
					contact.setUser_type("2");
					contact.setDisplayName(c.getString(c
							.getColumnIndex(TS_GROUP_USER.DISPLAYNAME)));
					contact.setLinkPhone(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USER_ACCOUNT)));
					contact.setUser_status(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USER_STATUS)));
					contact.setCreate_time(c.getString(c
							.getColumnIndex(TS_GROUP_USER.CREATE_DATE)));
					contact.setCreate_user(c.getString(c
							.getColumnIndex(TS_GROUP_USER.CREATE_USER)));
					contact.setGu_remark(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GU_REMARK)));
					contact.setIsAut(c.getString(c
							.getColumnIndex(TS_GROUP_USER.ISAUT)));
					contact.setFirstPy(c.getString(c
							.getColumnIndex(TS_GROUP_USER.SPNAME)));
					contact.setFnamefpynum(c.getInt(c
							.getColumnIndex(TS_GROUP_USER.FNAME_FPY_NUM)));
					contact.setAllPy(c.getString(c
							.getColumnIndex(TS_GROUP_USER.QPNAME)));
					contact.setCompany(c.getString(c
							.getColumnIndex(TS_GROUP_USER.COMPANY)));
					contact.setDepartment(c.getString(c
							.getColumnIndex(TS_GROUP_USER.DEPARTMENT)));
					contact.setJobprofession(c.getString(c
							.getColumnIndex(TS_GROUP_USER.JOBPROFESSION)));
					contact.setISJTUSER(c.getString(c
							.getColumnIndex(TS_GROUP_USER.ISJTUSER)));
					contact.setInstall(c.getInt(c
							.getColumnIndex(TS_GROUP_USER.INSTALL)));
					contact.setSourceId("4");
					contacts.add(contact);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			closeDB();
		}
		return contacts;
	}

	/**
	 * 根据姓名、拼音、电话号码查询成员
	 * 
	 * @return
	 */
	public List<Contact> queryByContactForGroupSelect(String str,
			boolean OnlyNeedInstalled) {
		List<Contact> contacts = new ArrayList<Contact>();
		SQLiteDatabase db = getReadableDatabase();
		String expandWhere = "";
		if (OnlyNeedInstalled) {
			expandWhere = " and TS_GROUP_USER.INSTALL=1 ";
		}
		Cursor c = null;
		String selectNames = "TS_GROUP_USER.GU_UUID" + ","
				+ "TS_GROUP_USER.GROUP_ID" + "," + "TS_GROUP_USER.DISPLAYNAME"
				+ "," + "TS_GROUP_USER.USER_ACCOUNT" + ","
				+ "TS_GROUP_USER.USER_STATUS" + ","
				+ "TS_GROUP_USER.CREATE_DATE" + ","
				+ "TS_GROUP_USER.CREATE_USER" + "," + "TS_GROUP_USER.GU_REMARK"
				+ "," + "TS_GROUP_USER.ISAUT" + "," + "TS_GROUP_USER.SPNAME"
				+ "," + "TS_GROUP_USER.FNAME_FPY_NUM" + ","
				+ "TS_GROUP_USER.QPNAME" + "," + "TS_GROUP_USER.COMPANY" + ","
				+ "TS_GROUP_USER.DEPARTMENT" + ","
				+ "TS_GROUP_USER.JOBPROFESSION" + ","
				+ "TS_GROUP_USER.ISJTUSER" + "," + "TS_GROUP_USER.INSTALL"
				+ "," + "TS_GROUP_NAME.GROUP_NAME"
				+ ",TS_GROUP_NAME.FNAME_FPY_NUM";

		String joins = " left join " + AppDbHelper.TABLE_GROUP + " on "
				+ TS_GROUP_USER.GROUP_ID + "=" + TS_GROUP_NAME.UUID;
		String sql = "";
		try {
			// 是否为电话号码
			if (StringUtil.isNumeric(str)) {
				// sql="select "+selectNames+" from TS_GROUP_USER "+joins+" where TS_GROUP_USER.USER_ACCOUNT like '%"+str+"%'  order by TS_GROUP_USER.GROUP_ID desc,TS_GROUP_USER.FNAME_FPY_NUM asc";
				c = db.rawQuery(
						"select "
								+ selectNames
								+ " from TS_GROUP_USER "
								+ joins
								+ " where TS_GROUP_USER.USER_ACCOUNT like '%"
								+ str
								+ "%' "
								+ expandWhere
								+ "  order by TS_GROUP_NAME.FNAME_FPY_NUM asc,TS_GROUP_USER.FNAME_FPY_NUM asc",
						null);
			}// 是否为拼音
			else if (StringUtil.isLetter(str)) {
				// sql=
				// "select  "+selectNames+"  from TS_GROUP_USER "+joins+"  where TS_GROUP_USER.QPNAME like '%"+str+"%' or TS_GROUP_USER.SPNAME like '%"+str+"%' order by TS_GROUP_USER.GROUP_ID desc,TS_GROUP_USER.FNAME_FPY_NUM asc";
				c = db.rawQuery(
						"select  "
								+ selectNames
								+ "  from TS_GROUP_USER "
								+ joins
								+ "  where TS_GROUP_USER.QPNAME like '%"
								+ str
								+ "%' or TS_GROUP_USER.SPNAME like '%"
								+ str
								+ "%'  "
								+ expandWhere
								+ " order by TS_GROUP_NAME.FNAME_FPY_NUM asc,TS_GROUP_USER.FNAME_FPY_NUM asc",
						null);
			}// 汉字
			else {
				// sql="select  "+selectNames+"  from TS_GROUP_USER "+joins+"  where TS_GROUP_USER.DISPLAYNAME like '%"+str+"%' order by TS_GROUP_USER.GROUP_ID desc,TS_GROUP_USER.FNAME_FPY_NUM asc";
				c = db.rawQuery(
						"select  "
								+ selectNames
								+ "  from TS_GROUP_USER "
								+ joins
								+ "  where TS_GROUP_USER.DISPLAYNAME like '%"
								+ str
								+ "%'  "
								+ expandWhere
								+ "  order by TS_GROUP_NAME.FNAME_FPY_NUM asc,TS_GROUP_USER.FNAME_FPY_NUM asc",
						null);
			}
			if (c != null) {
				while (c.moveToNext()) {
					Contact contact = new Contact();
					contact.setIdInGroup(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GU_UUID)));
					contact.setGroup_id(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GROUP_ID)));
					contact.setUser_type("2");
					contact.setDisplayName(c.getString(c
							.getColumnIndex(TS_GROUP_USER.DISPLAYNAME)));
					contact.setLinkPhone(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USER_ACCOUNT)));
					contact.setUser_status(c.getString(c
							.getColumnIndex(TS_GROUP_USER.USER_STATUS)));
					contact.setCreate_time(c.getString(c
							.getColumnIndex(TS_GROUP_USER.CREATE_DATE)));
					contact.setCreate_user(c.getString(c
							.getColumnIndex(TS_GROUP_USER.CREATE_USER)));
					contact.setGu_remark(c.getString(c
							.getColumnIndex(TS_GROUP_USER.GU_REMARK)));
					contact.setIsAut(c.getString(c
							.getColumnIndex(TS_GROUP_USER.ISAUT)));
					contact.setFirstPy(c.getString(c
							.getColumnIndex(TS_GROUP_USER.SPNAME)));
					contact.setFnamefpynum(c.getInt(c
							.getColumnIndex(TS_GROUP_USER.FNAME_FPY_NUM)));
					contact.setAllPy(c.getString(c
							.getColumnIndex(TS_GROUP_USER.QPNAME)));
					contact.setCompanyPath(c.getString(c
							.getColumnIndex(TS_GROUP_USER.COMPANY)));
					contact.setCompany(c.getString(c
							.getColumnIndex(TS_GROUP_USER.COMPANY)));
					contact.setDepartment(c.getString(c
							.getColumnIndex(TS_GROUP_USER.DEPARTMENT)));
					contact.setJobprofession(c.getString(c
							.getColumnIndex(TS_GROUP_USER.JOBPROFESSION)));
					contact.setISJTUSER(c.getString(c
							.getColumnIndex(TS_GROUP_USER.ISJTUSER)));
					contact.setInstall(c.getInt(c
							.getColumnIndex(TS_GROUP_USER.INSTALL)));
					contact.setSourceId("4");
					contact.setGroupName(c.getString(c
							.getColumnIndex(TS_GROUP_NAME.GROUP_NAME)));
					contacts.add(contact);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (c != null) {
				c.close();
			}
			closeDB();
		}
		return contacts;
	}

	/**
	 * 群组成员数量
	 * 
	 * @return
	 */
	public HashMap<String, Integer> queryGroupCount() {
		HashMap<String, Integer> map = new HashMap<String, Integer>();
		Cursor cursor = null;
		SQLiteDatabase db = getReadableDatabase();
		try {
			cursor = db
					.rawQuery(
							"select GROUP_ID,count(*) from ts_group_user group by GROUP_ID",
							null);
			if (cursor != null) {
				while (cursor.moveToNext()) {
					String groupId = cursor.getString(0);
					int count = cursor.getInt(1);
					map.put(groupId, count);
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			closeDB();
		}
		return map;

	}

	/**
	 * 根据群组id 号码去查询成员id
	 * 
	 * @param groupId
	 * @param phone
	 * @return
	 */
	public String queryMemberIdByPhone(String groupId, String phone) {
		String memberid = "";
		String sql = "select " + AppDbHelper.TS_GROUP_USER.GU_UUID + " from "
				+ AppDbHelper.TABLE_GROUP_USER + " where "
				+ AppDbHelper.TS_GROUP_USER.USER_ACCOUNT + "=" + phone
				+ " and " + AppDbHelper.TS_GROUP_USER.GROUP_ID + "=" + groupId;
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = null;
		try {
			cursor = db.rawQuery(sql, null);
			if (cursor != null && cursor.getCount() > 0) {
				if (cursor.moveToNext()) {
					memberid = cursor.getString(0);
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			closeDB();
		}
		return memberid;

	}

	/**
	 * 根据群组id 查询 群组url
	 * 
	 * @param groupId
	 * @return
	 */
	public String queryGroupPhoto(String groupId) {
		String url = "";
		String sql = "select " + AppDbHelper.TS_GROUP_NAME.IMAGEPATH + " from "
				+ AppDbHelper.TABLE_GROUP + " where "
				+ AppDbHelper.TS_GROUP_NAME.UUID + "=" + groupId;
		SQLiteDatabase db = getReadableDatabase();
		Cursor cursor = null;
		try {
			cursor = db.rawQuery(sql, null);
			if (cursor != null && cursor.getCount() > 0) {
				if (cursor.moveToNext()) {
					url = cursor.getString(0);
				}
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (cursor != null) {
				cursor.close();
			}
			closeDB();
		}
		return url;
	}
}
